package student.Dao;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import org.apache.commons.lang3.StringUtils;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import student.Student;

public class StudentDaoImpl implements StudentDao {
	Connection conn = null;
	Statement st = null;
	ResultSet rs = null;
	private String driver;
	private String url;
	private String user;
	private String pass;
	private Logger logger = LogManager.getLogger(LogManager.ROOT_LOGGER_NAME);

	public StudentDaoImpl() throws FileNotFoundException, IOException, ClassNotFoundException {
		this.initParam("mysql.ini");
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, user, pass);
			st = conn.createStatement();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void initParam(String paramfile) throws FileNotFoundException, IOException {
		Properties props = new Properties();
		props.load(new FileInputStream(paramfile));
		driver = props.getProperty("driver");
		url = props.getProperty("url");
		user = props.getProperty("user");
		pass = props.getProperty("pass");
	}

	public void createTable() throws Exception {
		Class.forName(driver);
		String sql = "create table jdbc_test" + "( jdbc_id int auto_increment primary key," + "jdbc_name varchar(255),"
				+ "jdbc_desc text)";
		try (Connection conn = DriverManager.getConnection(url, user, pass);

				Statement stmt = conn.createStatement()) {
			stmt.executeUpdate(sql);
		}
	}

	public void create(Student student) {

		try {
			String sql = "INSERT INTO STU( ID , CLASS , CODE , FULLNAME ) VALUES(" + null + ",'"
					+ student.getClassAndGrades() + "','" + student.getCode() + "','" + student.getFullName() + "')";
			logger.debug(student);
			int result = st.executeUpdate(sql);

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (st != null) {
					st.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void remove(String code) {

		try {
			String sql = "DELETE FROM STU WHERE CODE = " + code;
			logger.debug("删除成功！");
			int result = st.executeUpdate(sql);

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (st != null) {
					st.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void update(Student student) {

		try {
			String sql = "UPDATE STU SET CLASS = '" + student.getClassAndGrades() + "' , FULLNAME = '"
					+ student.getFullName() + "' WHERE CODE = " + student.getCode();
			logger.debug(student);
			int result = st.executeUpdate(sql);

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (st != null) {
					st.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		/*
		 * String valueString = "VALUES (" + student.getId() + "," +
		 * student.getFullName() + " " + student.getCode() + ")"; String sql =
		 * "UPDATE INTO STU " + valueString; try { int num = this.st.executeUpdate(sql);
		 * } catch (SQLException e) { // TODO Auto-generated catch block
		 * e.printStackTrace(); }
		 */
	}

	public Student findByName(String name) {
		Student student = null;
		try {
//			String selectSQL = "SELECT ID, FULLNAME FROM T_STUDENT WHERE ID = ? ";
			String selectSQL = "SELECT ID, CLASS, CODE, FULLNAME FROM STU WHERE FULLNAME =  " + name;
//			PreparedStatement ps = this.con.prepareStatement(selectSQL);
//			ps.setLong(1, id);					
			ResultSet rs = this.st.executeQuery(selectSQL);
			while (rs.next()) {
				student = new Student();
				student.setId(rs.getLong("ID"));
				student.setClassAndGrades(rs.getString("CLASS"));
				student.setCode(rs.getString("CODE"));
				student.setFullName(rs.getString("FULLNAME"));

//				logger.info("{ID:" + rs.getLong(1) + "\t fullname:" + rs.getString(2) + "}");
				if (student.getFullName() == name) {
					logger.info("found student:" + student);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (st != null) {
					st.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return student;
	}

	public Student findByCode(String code) {
		Student student = null;
		try {
//			String selectSQL = "SELECT ID, FULLNAME FROM T_STUDENT WHERE ID = ? ";
			String selectSQL = "SELECT ID, CLASS, CODE, FULLNAME FROM STU WHERE CODE =  " + code;
//			PreparedStatement ps = this.con.prepareStatement(selectSQL);
//			ps.setLong(1, id);					
			ResultSet rs = this.st.executeQuery(selectSQL);
			while (rs.next()) {
				student = new Student();
				student.setId(rs.getLong("ID"));
				student.setClassAndGrades(rs.getString("CLASS"));
				student.setCode(rs.getString("CODE"));
				student.setFullName(rs.getString("FULLNAME"));

//				logger.info("{ID:" + rs.getLong(1) + "\t fullname:" + rs.getString(2) + "}");
				if (student.getCode() == code) {
					logger.info("found student:" + student);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (st != null) {
					st.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return student;
	}

	/*public static void main(String[] args) throws Exception {
		StudentDaoImpl stu = new StudentDaoImpl();
		stu.createTable();
	}*/
}
